package easytodo.client.data;

import java.util.ArrayList;
import java.util.NoSuchElementException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.Time;
import easytodo.model.Category;
import easytodo.model.Note;

public class SQLiteNoteHandlerImpl extends SQLiteOpenHelper implements
		NoteHandler {

	private OnNoteChangedListener noteChangedListener = null;

	public void setOnNoteChangedListener(OnNoteChangedListener l) {
		noteChangedListener = l;
	}

	public SQLiteNoteHandlerImpl(Context context) {
		super(context, "NoteDatabase", null, 6);
	}

	// private void notifyChangedListenerRefresh() {
	// if (noteChangedListener != null)
	// noteChangedListener.onNotesChanged();
	// }

	private void notifyChangedListenerNoteChanged(long result, Boolean isRemoved) {
		if (noteChangedListener != null)
			noteChangedListener.onNoteChanged(result, isRemoved);
	}

	public void saveNote(Note note) {
		SQLiteDatabase db = getWritableDatabase();

		ContentValues cv = getNoteContentValues(note, false);

		long result = db.insert("Note", null, cv);

		db.close();

		if (result != -1)
			notifyChangedListenerNoteChanged(result, false);
	}

	private ContentValues getNoteContentValues(Note note, Boolean isModified) {
		ContentValues cv = new ContentValues();

		cv.put("Title", (String) note.Title);
		cv.put("Content", (String) note.Content);
		cv.put("PriorityID", note.PriorityID);
		cv.put("CategoryID", note.CategoryID);

		Time time = new Time();
		time.setToNow();

		if (isModified) {
			cv.put("Modified", time.toMillis(false));
		} else {
			cv.put("Created", time.toMillis(false));
		}

		return cv;
	}

	public void removeNote(int id) {
		SQLiteDatabase db = getWritableDatabase();

		long result = db.delete("Note", "ID=?",
				new String[] { String.valueOf(id) });

		db.close();

		if (result != 0)
			notifyChangedListenerNoteChanged(id, true);
	}

	public void editNote(Note note) {
		Note dbNote = null;
		SQLiteDatabase db = null;
		try {
			dbNote = getNote(note.ID);

			if (dbNote != null) {
				db = getWritableDatabase();

				ContentValues cv = getNoteContentValues(note, true);

				db.update("Note", cv, "ID=?",
						new String[] { String.valueOf(note.ID) });

				notifyChangedListenerNoteChanged(note.ID, false);
			}
		} catch (Exception e) {
		} finally {
			if (db != null)
				db.close();
		}
	}

	public Note getNote(long id) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor data = null;
		Note note = null;
		try {
			String[] selArgs = new String[] { String.valueOf(id) };
			data = db.query("Note", null, "ID = ?", selArgs, null, null, null);

			if (!data.moveToFirst())
				throw new NoSuchElementException();

			note = getNoteFromCursor(data);

		} catch (Exception e) {
			note = null;
		} finally {
			db.close();
			data.close();
		}

		return note;

	}

	private Note getNoteFromCursor(Cursor data) {
		Note note = new Note();
		
		note.ID = data.getInt(data.getColumnIndex("ID"));
		note.Title = data.getString(data.getColumnIndex("Title"));
		note.Content = data.getString(data.getColumnIndex("Content"));
		note.Created = data.getLong(data.getColumnIndex("Created"));
		note.Modified = data.getLong(data.getColumnIndex("Modified"));
		note.CategoryID = data.getInt(data.getColumnIndex("CategoryID"));
		note.PriorityID = data.getInt(data.getColumnIndex("PriorityID"));
		
		return note;
	}

	public ArrayList<Note> getAllNotes() {
		SQLiteDatabase db = getReadableDatabase();
		Cursor data = null;
		ArrayList<Note> notes = null;
		try {
			data = db.query("Note", null, null, null, null, null, "PriorityID");

			if (!data.moveToFirst())
				throw new NoSuchElementException();

			notes = new ArrayList<Note>();
			while (!data.isAfterLast()) {

				Note note = getNoteFromCursor(data);

				notes.add(note);

				data.moveToNext();
			}

		} catch (Exception e) {
			notes = new ArrayList<Note>();
		} finally {
			db.close();
			data.close();
		}
		return notes;
	}

	public ArrayList<Note> searchNote(String searchParameter,
			String[] searchColumns) {
		// TODO Auto-generated method stub
		return null;
	}

	public void copyNoteHandler(NoteHandler target) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			initTblPriority(db);
			initTblCategory(db);
			initTblNote(db);
			initTblTag(db);
			initTblNoteTag(db);
		} catch (Exception ex) {
		}
	}

	// Must be called before initTblNotes
	private void initTblCategory(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE Category (" + "ID INTEGER PRIMARY KEY,"
				+ "Title TEXT NOT NULL);");

		db.execSQL("INSERT INTO Category (Title) VALUES('TODO');");
		db.execSQL("INSERT INTO Category (Title) VALUES('TOSEE');");
		db.execSQL("INSERT INTO Category (Title) VALUES('TOBUY');");
	}

	// Must be called before initTblNotesTags
	private void initTblTag(SQLiteDatabase db) {
		db.execSQL(("CREATE TABLE Tag (" + "ID INTEGER PRIMARY KEY,"
				+ " Title TEXT NOT NULL);"));
	}

	private void initTblNoteTag(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE NoteTag (" + "NoteID INT NOT NULL,"
				+ " TagID INT NOT NULL," + " PRIMARY KEY (NoteID, TagID),"
				+ " FOREIGN KEY(NoteID) REFERENCES Note(ID),"
				+ " FOREIGN KEY(TagID) REFERENCES Tag(ID));");
	}

	// Must be called before initTblNotes
	private void initTblPriority(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE Priority (" + "ID INTEGER PRIMARY KEY,"
				+ " Value INT NOT NULL," + " Title TEXT NOT NULL);");

		db.execSQL("INSERT INTO Priority (Title, Value) VALUES ('Low', 0);");
		db.execSQL("INSERT INTO Priority (Title, Value) VALUES ('Medium', 1);");
		db.execSQL("INSERT INTO Priority (Title, Value) VALUES ('High', 2);");
		db.execSQL("INSERT INTO Priority (Title, Value) VALUES ('DoItNow', 3);");

	}

	// Must be called before initTblNotesTags
	private void initTblNote(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE Note (ID INTEGER PRIMARY KEY,"
				+ " Created INT NOT NULL,"
				+ " Modified INT,"
				+ " Title TEXT NOT NULL,"
				+ " Content TEXT NOT NULL,"
				+ " PriorityID INT NOT NULL,"
				+ " CategoryID INT NOT NULL, FOREIGN KEY(PriorityID) REFERENCES Priority(ID), FOREIGN KEY(CategoryID) REFERENCES Category(ID));");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			db.execSQL("DROP TABLE IF EXISTS tblNote");
			db.execSQL("DROP TABLE IF EXISTS tblCategory");
			db.execSQL("DROP TABLE IF EXISTS tblPriority");
			db.execSQL("DROP TABLE IF EXISTS tblTag");
			db.execSQL("DROP TABLE IF EXISTS tblNoteTag");

			db.execSQL("DROP TABLE IF EXISTS Note");
			db.execSQL("DROP TABLE IF EXISTS Category");
			db.execSQL("DROP TABLE IF EXISTS Priority");
			db.execSQL("DROP TABLE IF EXISTS Tag");
			db.execSQL("DROP TABLE IF EXISTS NoteTag");

			initTblPriority(db);
			initTblCategory(db);
			initTblNote(db);
			initTblTag(db);
			initTblNoteTag(db);
		} catch (Exception ex) {
		}
	}

	public ArrayList<Category> getCategories() {
		// TODO Auto-generated method stub
		return null;
	}
}
